package siac.com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class JDBCConexao {
	public static final int MOTOR_MYSQL = 0, MOTOR_ORACLE = MOTOR_MYSQL + 1,
			MOTOR_POSTGRESQL = MOTOR_ORACLE + 1,
			MOTOR_SQLSERVER = MOTOR_POSTGRESQL + 1,
			MOTOR_FIREBIRD = MOTOR_SQLSERVER + 1;

	private static final String jdbcDS[] = { "jdbc/dbtxcambio", "jdbc/dbsigma" };
	public static final int DS_TXCAMBIO = 0, DS_SIGMA = DS_TXCAMBIO + 1;

	private static final String jdbcDriver[] = { "com.mysql.jdbc.Driver",
			"oracle.jdbc.driver.OracleDriver", "org.postgresql.Driver",
			"com.microsoft.jdbc.sqlserver.SQLServerDriver",
			"org.firebirdsql.jdbc.FBDriver" };

	private String dbUrl[] = { "jdbc:mysql://", "jdbc:oracle:thin:@",
			"jdbc:postgresql://", "jdbc:microsoft:sqlserver://",
			"jdbc:firebirdsql:" };

	private static final String dbUrl2[] = { "jdbc:mysql://localhost:3306/",
			"jdbc:oracle:thin:@10.1.0.123:1521:",
			"jdbc:postgresql://localhost:5432/",
			"jdbc:microsoft:sqlserver://localhost:1433/",
			"jdbc:firebirdsql:localhost:" };

	// acesso a base de dados
	private String dbHost;
	private String dbPort;
	private String dbName;
	private String dbUser;
	private String dbPwd;
	// manages connection
	private String driverManager;
	private Connection conexao;
	private Statement stmt;
	private PreparedStatement pstmt;
	private JDBCNamedParameterStatement pstmtNamed;

	// private ResultSet rs;
	// private ResultSetMetaData metaData;

	public JDBCConexao(String dbHost, String dbPort, String dbName,
			String dbUser, String dbPwd) {
		super();
		this.dbHost = dbHost;
		this.dbPort = dbPort;
		this.dbName = dbName;
		this.dbUser = dbUser;
		this.dbPwd = dbPwd;
	}

	public void criaConexaoDS(int motor) throws NamingException, SQLException {
		Context ctx = new InitialContext();
		if (ctx == null) {
			throw new NamingException("No initial context");
		}

		Context envContext = (Context) ctx.lookup("java:/comp/env");
		if (envContext == null) {
			throw new NamingException("No environment context");
		}

		DataSource ds = (DataSource) envContext.lookup(jdbcDS[motor]);
		// OracleDataSource ds = (OracleDataSource)
		// envContext.lookup(jdbcDS[motor]);
		if (ds == null) {
			throw new NamingException("No data source");
		}

		conexao = ds.getConnection();
		if (conexao == null) {
			throw new SQLException("No database connection");
		}
		// return conexao;
	}

	public void criaConexaoDM(int motor) throws ClassNotFoundException,
			SQLException {
		switch (motor) {
		case MOTOR_MYSQL:
			driverManager = dbUrl[MOTOR_MYSQL] + dbHost + ":" + dbPort + "/"
					+ dbName;
			break;
		case MOTOR_ORACLE:
			driverManager = dbUrl[MOTOR_ORACLE] + dbHost + ":" + dbPort + ":"
					+ dbName;
			break;
		case MOTOR_POSTGRESQL:
			driverManager = dbUrl[MOTOR_POSTGRESQL] + dbHost + ":" + dbPort
					+ "/" + dbName;
			break;
		case MOTOR_SQLSERVER:
			driverManager = dbUrl[MOTOR_SQLSERVER] + dbHost + ":" + dbPort
					+ "/" + dbName;
			dbUser = " User=" + dbUser;
			dbPwd = " Password=" + dbPwd;
			break;
		case MOTOR_FIREBIRD:
			driverManager = dbUrl[MOTOR_FIREBIRD] + dbHost + ":" + dbName;
			break;
		default:
			break;
		}

		Class.forName(jdbcDriver[motor]);
		conexao = DriverManager.getConnection(driverManager, dbUser, dbPwd);

		if (conexao == null) {
			throw new SQLException("No database connection");
		}
		// return conexao;
	}

	// /////////////////////////////////////////////

	public void criaNamedPreparedStatment(String sql) throws SQLException {
		pstmtNamed = new JDBCNamedParameterStatement(conexao, sql);
	}

	public void criaPreparedStatment(String sql) throws SQLException {
		pstmt = conexao.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
	}

	public void criaStatment() throws SQLException {
		stmt = conexao.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
	}

	public boolean executeComNamedParameter() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmtNamed.execute();
	}

	public boolean executeComPreparedStatement() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmt.execute();
	}

	public int executeUpdateComNamedParameter() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmtNamed.executeUpdate();
	}

	public int executaUpdateComPreparedStatement() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmt.executeUpdate();
	}

	public int executaUpdateComStatement(String sql) throws SQLException {
		// stmt.setQueryTimeout(30);
		return stmt.executeUpdate(sql);
	}

	public ResultSet executaQueryComNamedParameter() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmtNamed.executeQuery();
	}

	public ResultSet executaQueryComPreparedStatement() throws SQLException {
		// pstmt.setQueryTimeout(30);
		return pstmt.executeQuery();
	}

	public ResultSet executaQueryComStatement(String query) throws SQLException {
		// stmt.setQueryTimeout(30);
		return stmt.executeQuery(query);
	}

	public ResultSetMetaData getMetaData4PreparedStatement()
			throws SQLException {
		return pstmt.getMetaData();
	}

	public int getProximoID(String tabela, String campo) throws SQLException {
		int id = 0;
		String sql = "SELECT max(" + campo + ") AS " + campo + " FROM "
				+ tabela;
		ResultSet rs = executaQueryComStatement(sql);
		if (rs.next()) {
			id = rs.getInt(campo) + 1;
		}
		return id;
	}

	public void commitTransaction() throws SQLException {
		conexao.commit(); // commit update
	}

	public void rollbackTransaction() throws SQLException {
		conexao.rollback(); // rollback update
	}

	/**
	 * Assegura que as instatementancias resultSet, preparedStatement e conexao
	 * serao encerrados devidamente.
	 */
	public void fecharConexao() {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (pstmtNamed != null) {
				pstmtNamed.close();
			}
			if (conexao != null) {
				conexao.close();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public String[] getDbUrl() {
		return dbUrl;
	}

	public void setDbUrl(String[] dbUrl) {
		this.dbUrl = dbUrl;
	}

	public String getDbHost() {
		return dbHost;
	}

	public void setDbHost(String dbHost) {
		this.dbHost = dbHost;
	}

	public String getDbPort() {
		return dbPort;
	}

	public void setDbPort(String dbPort) {
		this.dbPort = dbPort;
	}

	public String getDbName() {
		return dbName;
	}

	public void setDbName(String dbName) {
		this.dbName = dbName;
	}

	public String getDbUser() {
		return dbUser;
	}

	public void setDbUser(String dbUser) {
		this.dbUser = dbUser;
	}

	public String getDbPwd() {
		return dbPwd;
	}

	public void setDbPwd(String dbPwd) {
		this.dbPwd = dbPwd;
	}

	public String getDriverManager() {
		return driverManager;
	}

	public void setDriverManager(String driverManager) {
		this.driverManager = driverManager;
	}

	public Connection getConexao() {
		return conexao;
	}

	public void setConexao(Connection conexao) {
		this.conexao = conexao;
	}

	public Statement getStmt() {
		return stmt;
	}

	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}

	public JDBCNamedParameterStatement getPstmtNamed() {
		return pstmtNamed;
	}

	public void setPstmtNamed(JDBCNamedParameterStatement pstmtNamed) {
		this.pstmtNamed = pstmtNamed;
	}

	public PreparedStatement getPstmt() {
		return pstmt;
	}

	public void setPstmt(PreparedStatement pstmt) {
		pstmt = pstmt;
	}

	public String[] getJdbcdriver() {
		return jdbcDriver;
	}

	public String[] getDburl() {
		return dbUrl;
	}

	public static String[] getDburl2() {
		return dbUrl2;
	}

}
